package jp.bsws.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import jp.bsws.bean.JugyoinBean;
import jp.bsws.bean.KeyValueBean;
import jp.bsws.bean.KeyValueListBean;
import jp.bsws.common.BSWSSession;
import jp.bsws.common.BSWSUtils;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class JugyoinDao {
	private static JugyoinDao jugyoinDao = new JugyoinDao();

	/** ログ */
	private Log log = LogFactory.getLog(JugyoinDao.class);

	/**
	 * コンストラクタ
	 */
	// Singletonパターンで単一のインスタンスを取得
	public static JugyoinDao getInstance(){
		return jugyoinDao;
	}

	private String SQL_JUGYOIN_DELETE =
		" DELETE FROM " +
		"     M_JUGYOIN " +
		" WHERE " +
		"     SEQ = ? ";

	/**
	 * 店舗情報削除フラグ変更
	 */
	public int deleteJugyoin(Connection con, Integer intTaisyoSeq) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_JUGYOIN_DELETE);
		stmt.setInt(1, intTaisyoSeq);

		int deleteCnt = stmt.executeUpdate();

		stmt.close();

		return deleteCnt;
	}

	private String SQL_JUGYOIN_INSERT =
		" INSERT INTO " +
		"     M_JUGYOIN ( " +
		"         SEQ " +
		"        ,GROUP_COD " +
		"        ,TENPO_COD " +
		"        ,JUGYOIN_COD " +
		"        ,JUGYOIN_KNJ " +
		"        ,PASSWORD " +
		"        ,KENGEN_SEQ " +
		"        ,NYUTEN_YMD " +
		"        ,TAITEN_YMD " +
		"        ,HYOJI_JUN " +
		"        ,TOROKU_TMS " +
		"        ,TOROKUSHA_COD " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 従業員情報追加
	 */
	public int insertJugyoin(Connection con, Integer seq, BSWSSession bSWSSession , JugyoinBean jugyoinBean) throws Exception{

		BSWSDao bSWSDao = BSWSDao.getInstance();
		//最大値を取得
		Integer maxSeq = bSWSDao.getMaxSeq(con, "M_JUGYOIN");
		if(seq == null){
			seq = maxSeq + 1;
		}

		PreparedStatement stmt = con.prepareStatement(SQL_JUGYOIN_INSERT);
		stmt.setInt(1, seq);
		stmt.setString(2, bSWSSession.getStrGroupCod());
		stmt.setString(3, jugyoinBean.getStrTenpoCod());
		stmt.setString(4, jugyoinBean.getStrJugyoinCod());
		stmt.setString(5, jugyoinBean.getStrJugyoinKnj());
		stmt.setString(6, jugyoinBean.getStrPassword());
		stmt.setInt(7, jugyoinBean.getIntKengenSeq());
		stmt.setInt(8, Integer.parseInt(jugyoinBean.getStrNyutenYmd()));
		stmt.setInt(9, Integer.parseInt(jugyoinBean.getStrTaitenYmd()));
		stmt.setInt(10, Integer.parseInt(jugyoinBean.getStrHyojiJun()));
		stmt.setString(11, bSWSSession.getStrJugyoinCod());
		stmt.setString(12, bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_JUGYOIN_UPDATE =
		" UPDATE " +
		"     M_JUGYOIN " +
		" SET " +
		"         TENPO_COD = ? " +
		"        ,JUGYOIN_COD = ? " +
		"        ,JUGYOIN_KNJ = ? " +
		"        ,PASSWORD = ? " +
		"        ,KENGEN_SEQ = ? " +
		"        ,NYUTEN_YMD = ? " +
		"        ,TAITEN_YMD = ? " +
		"        ,HYOJI_JUN = ? " +
		"        ,KOSHIN_TMS = now() " +
		"        ,KOSHINSHA_COD = ? " +
		" WHERE " +
		"     SEQ = ? ";

	/**
	 * 従業員情報更新
	 */
	public int updateJugyoin(Connection con,BSWSSession bSWSSession, JugyoinBean jugyoinBean) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_JUGYOIN_UPDATE);

		stmt.setString(1, jugyoinBean.getStrTenpoCod());
		stmt.setString(2, jugyoinBean.getStrJugyoinCod());
		stmt.setString(3, jugyoinBean.getStrJugyoinKnj());
		stmt.setString(4, jugyoinBean.getStrPassword());
		stmt.setInt(5, jugyoinBean.getIntKengenSeq());
		stmt.setInt(6, Integer.parseInt(jugyoinBean.getStrNyutenYmd()));
		stmt.setInt(7, Integer.parseInt(jugyoinBean.getStrTaitenYmd()));
		stmt.setInt(8, Integer.parseInt(jugyoinBean.getStrHyojiJun()));
		stmt.setString(9, bSWSSession.getStrJugyoinCod());
		stmt.setInt(10, jugyoinBean.getSeq());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_KV = " SELECT SEQ, JUGYOIN_KNJ FROM M_JUGYOIN WHERE GROUP_COD = ? ";

	/**
	 * 店舗に登録されている従業員情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<KeyValueBean> getJugyoinList(Connection con, String pkGroupCod, String pkTenpoCod) throws Exception{
		List<KeyValueBean> resultList = new ArrayList<KeyValueBean>();

		//検索条件の作成
		String where = "";
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += " AND TENPO_COD = ? ";
		}

		String orderBy = " ORDER BY HYOJI_JUN ";
		PreparedStatement stmt = con.prepareStatement(SQL_KV + where + orderBy);

		stmt.setString(1, pkGroupCod);
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			stmt.setString(2, pkTenpoCod);
		}
		ResultSet rs = stmt.executeQuery();

		KeyValueBean kvBean = null;
		while(rs.next()){
			 kvBean = new KeyValueBean();
			String key = rs.getString("SEQ");
			String value = rs.getString("JUGYOIN_KNJ");
			kvBean.setKey(key);
			kvBean.setValue(value);
			resultList.add(kvBean);
		}
		rs.close();
		stmt.close();

		return resultList;
	}
}